﻿using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using WMS.FC.Domain.DomainObject.System;
using WMS.FC.Domain.Repository;
using WMS.FC.Infrastructure.Common;

namespace WMS.FC.Infrastructure.Repository
{
    public class SystemRepository : RepositoryBase, ISystemRepository
    {
        protected readonly IOptions<WmsSettings> options;

        public SystemRepository(WmsDbContext dbContext, IOptions<WmsSettings> options) : base(dbContext)
        {
            this.options = options;
        }

        public async Task<DataTable> ExecuteQuery(string sql, Dictionary<string, object>? parameterDic = null, Pagination? pagination = null, Sorter? sorter = null, UserInfo? userInfo = null)
        {
            var tempSql = GenerateSql(sql, parameterDic, pagination, sorter);
            return await ExecuteQuery(tempSql, GetQueryParameterList(parameterDic, userInfo));
        }

        public async Task<int> ExecuteQueryCount(string sql, Dictionary<string, object>? parameterDic = null, UserInfo? userInfo = null)
        {
            var tempSql = GenerateSql(sql, parameterDic, null, null);
            var result = await ExecuteScalar($"SELECT COUNT(1) FROM ({tempSql}) TEMPCOUNT", GetQueryParameterList(parameterDic, userInfo));
            if (result == null || !int.TryParse(result.ToString(), out var count))
                throw new Exception("查询行数异常");

            return count;
        }

        public async Task<DataTable?> ExecuteQuerySchema(string sql, Dictionary<string, object>? parameterDic = null, UserInfo? userInfo = null)
        {
            var tempSql = GenerateSql(sql, parameterDic, null, null);
            return await ExecuteSchema(tempSql, GetQueryParameterList(parameterDic, userInfo));
        }

        protected string GenerateSql(string sql, Dictionary<string, object>? parameters = null, Pagination? pagination = null, Sorter? sorter = null)
        {
            var result = sql;

            if (parameters != null)
            {
                result = $"SELECT * FROM ({result}) WHERE {string.Join(" AND ", parameters.Select(item => $"\"{item.Key}\"=:{item.Key}").ToList())}";
            }

            if (sorter != null)
            {
                result = $"SELECT * FROM ({result}) ORDER BY {sorter.SortField} {(sorter.SortOrder.Contains("desc", StringComparison.InvariantCultureIgnoreCase) ? "DESC" : "ASC")}";
            }

            if (pagination != null)
            {
                result = $"SELECT * FROM (SELECT TEMP.*,ROWNUM AS RN FROM ({result}) TEMP WHERE ROWNUM <= {pagination.PageNumber * pagination.PageSize}) TEMP1 WHERE TEMP1.RN > {(pagination.PageNumber - 1) * pagination.PageSize}";
            }

            return result;
        }

        protected async Task<DataTable> ExecuteQuery(string sql, List<OracleParameter>? parameterList)
        {
            var connection = dbContext.Database.GetDbConnection() as OracleConnection;
            var command = GetCommand(connection, sql, parameterList);
            var reader = await command.ExecuteReaderAsync() as OracleDataReader;
            return await ReadToDataTable(reader);
        }

        protected async Task<object?> ExecuteScalar(string sql, List<OracleParameter>? parameterList)
        {
            var connection = dbContext.Database.GetDbConnection() as OracleConnection;
            var command = GetCommand(connection, sql, parameterList);
            return await command.ExecuteScalarAsync();
        }

        protected async Task<DataTable?> ExecuteSchema(string sql, List<OracleParameter>? parameterList)
        {
            var connection = dbContext.Database.GetDbConnection() as OracleConnection;
            var command = GetCommand(connection, sql, parameterList);
            var reader = await command.ExecuteReaderAsync() as OracleDataReader;
            return await reader!.GetSchemaTableAsync();
        }

        protected async Task<DataTable> ReadToDataTable(OracleDataReader? reader)
        {
            ArgumentNullException.ThrowIfNull(reader);
            var dataTable = new DataTable();
            DataRow dataRow;
            for (var fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
            {
                dataTable.Columns.Add(reader.GetName(fieldIndex));
            }

            var rowLimit = options.Value.DbRowLimit;
            while (await reader.ReadAsync())
            {
                if (dataTable.Rows.Count > rowLimit)
                    throw new Exception($"查询数据不能一次超过{rowLimit}行");

                dataRow = dataTable.NewRow();
                for (var fieldIndex = 0; fieldIndex < reader.FieldCount; fieldIndex++)
                {
                    var data = reader[fieldIndex].ToString();
                    dataRow[fieldIndex] = data;
                }

                dataTable.Rows.Add(dataRow);
            }

            return dataTable;
        }

        protected OracleCommand GetCommand(OracleConnection? connection, string? sql, List<OracleParameter>? parameterList)
        {
            ArgumentNullException.ThrowIfNull(connection);
            ArgumentNullException.ThrowIfNull(sql);
            if (connection.State != ConnectionState.Open) connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = sql;
            command.BindByName = true;
            if (parameterList != null)
            {
                foreach (var parameter in parameterList)
                {
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }

        protected List<OracleParameter>? GetQueryParameterList(Dictionary<string, object>? parameterDic, UserInfo? userInfo)
        {
            var parameterList = new List<OracleParameter>();

            if (parameterDic == null || !parameterDic.ContainsKey("warehouse_code"))
                parameterList.Add(new OracleParameter("warehouse_code", options.Value.WarehouseCode));

            if (parameterDic == null || !parameterDic.ContainsKey("line_code"))
                parameterList.Add(new OracleParameter("line_code", options.Value.LineCode));

            if ((parameterDic == null || !parameterDic.ContainsKey("user_name")) && userInfo != null)
                parameterList.Add(new OracleParameter("user_name", userInfo.UserName));

            if ((parameterDic == null || !parameterDic.ContainsKey("user_ip")) && userInfo != null)
                parameterList.Add(new OracleParameter("user_ip", userInfo.UserIp));

            if (parameterDic == null) return parameterList;
            parameterList.AddRange(parameterDic.Select(item => new OracleParameter(item.Key, item.Value)).ToList());
            return parameterList;
        }

        public async Task<int> ExecuteNoQuery(string sql, UserInfo userInfo, Dictionary<string, object>? parameters = null)
        {
            return await dbContext.Database.ExecuteSqlRawAsync(sql, GetExecuteParameterList(userInfo, parameters));
        }

        protected List<OracleParameter> GetExecuteParameterList(UserInfo userInfo, Dictionary<string, object>? parameters = null)
        {
            var tempParameters = parameters;
            tempParameters ??= new Dictionary<string, object>();
            ReplaceKey(tempParameters, "id", SnowflakeIdHelper.Instance.NextId(), "0");
            ReplaceKey(tempParameters, "create_date", DateTime.Now);
            ReplaceKey(tempParameters, "created_by", userInfo.UserName);
            ReplaceKey(tempParameters, "modify_date", DateTime.Now);
            ReplaceKey(tempParameters, "modified_by", userInfo.UserName);
            ReplaceKey(tempParameters, "modify_ip", userInfo.UserIp);
            ReplaceKey(tempParameters, "warehouse_code", options.Value.WarehouseCode);
            ReplaceKey(tempParameters, "is_deleted", 0, "");
            return tempParameters.Select(item => new OracleParameter(item.Key, item.Value)).ToList();
        }

        protected void ReplaceKey(Dictionary<string, object> parameters, string key, object value, string? defaultValue = null)
        {
            if (!parameters.ContainsKey(key))
            {
                parameters.Add(key, value);
            }
            else
            {
                if (defaultValue != null)
                {
                    if (string.IsNullOrWhiteSpace(parameters[key].ToString()) || parameters[key].ToString() == defaultValue)
                    {
                        parameters[key] = value;
                    }
                }
                else
                {
                    parameters[key] = value;
                }
            }
        }
    }
}
